#!/usr/bin/perl

package evaluation4edu::Admin;
use strict;
use warnings;
use JSON ();
use DBI;
use Carp;
use Dancer ':syntax';
use Data::Dumper;
use 5.10.1;

sub new {
	my ( $self,$db_name ) = @_;
	#启动时进行DB连接
	my $db_conn=DBI->connect($db_name,'','', {
			RaiseError => 1,
			AutoCommit => 1,
			sqlite_unicode => 1,
		}
	) or croak '连接DB失败';

	#设置DBtrace
	$db_conn->trace(4,'dbi_trace.log');

	bless {
		db_conn => $db_conn,
	};
}

sub reload_db {
}

sub sub_call {
	my ( $self,$func_name ) = @_;
	defined $self or croak 'uninitialize';

	return{
		get_term => \&get_term,
		set_term => \&set_term,
		get_class => \&get_class,
		set_class => \&set_class,
		get_group => \&get_group,
		set_group => \&set_group,
		get_evaluation => \&get_evaluation,
		add_evaluation => \&add_evaluation,
		get_student => \&get_student,
		get_scoretype =>  \&get_scoretype,
	} -> {$func_name};
}

sub get_term {
	my ( $self,$arg ) = @_;
	defined $self or croak 'uninialize';

	#构造SQL并查询DB
	my $query = 'select Id,Name from t_Term';
	if( exists $arg->{term_id} and $#{ $arg->{term_id} } >= 0 ) {
		$query.= " where id in ( ".join(',',map { $self->{db_conn}->quote($_); } @{$arg->{term_id}}) . ")";
	}
	debug "sql:[$query]";

	my $db_result;
	eval {
		$db_result=$self->{db_conn}->selectall_arrayref($query);
	} or do {
		#查询失败
		error "database select failed [$@]";
		return ( 1,undef );
	};

	debug "select result: [$db_result]";

	my $resp = {
		term => [ map { { term_id => $_->[0], term_name => $_->[1] } } @{$db_result} ],
	};

	return (0,$resp);
}

sub set_term {
	my ( $self,$arg ) = @_;
	defined $self or croak 'uninialize';

	my ( $query, $db_result, $id );
	if ( !exists $arg->{term_id} ) {
		#先查询最大的id
		eval {
			$db_result=$self->{db_conn}->selectall_arrayref('select max(Id) from t_Term');
		} or do {
			#查询失败
			error "database select failed [$@]";
			return ( 1,undef );
		};
		debug Dumper( $db_result );
		$#{$db_result} >=0 and $id = $db_result->[0]->[0] + 1;
	}
	else {
		#更新现有学期
		$id = $arg->{term_id};
	}
	$query = 'replace into t_Term(Id,Name) values (' . $self->{db_conn}->quote($id) . ',' . $self->{db_conn}->quote($arg->{term_name}) . ')';
	debug "sql[$query]";

	eval {
		$db_result=$self->{db_conn}->do($query);
	} or do {
		error "execute sql[$query] failed [$@]";
		return ( 1,undef );
	};

	my $resp = {
		term_id => $id,
		term_name => $arg->{term_name},
	};

	return (0,$resp);
}

sub get_class {
	my ( $self,$arg ) = @_;
	defined $self or croak 'uninialize';

	#构造SQL并查询DB
	exists $arg->{term_id} or do {
		#没有指定term_id
		error "term_id not found";
		return ( 1,undef );
	};
	my $query = 'select Id,Term_Id,Name from t_Class where Term_Id=' . $self->{db_conn}->quote($arg->{term_id});

	if( exists $arg->{class_id} and $#{ $arg->{class_id} } >= 0 ) {
		$query.= " and Id in ( ".join(',',map { $self->{db_conn}->quote($_) } @{$arg->{class_id}}) . ")";
	}
	debug "sql:[$query]";

	my $db_result;
	eval {
		$db_result=$self->{db_conn}->selectall_arrayref($query);
	} or do {
		#查询失败
		error "database select failed [$@]";
		return ( 1,undef );
	};

	my $resp = {
	   	class => [ map { { class_id => $_->[0], term_id => $_->[1], class_name => $_->[2] } } @{$db_result} ],
	};

	return (0,$resp);
}

sub set_class {
	my ( $self,$arg ) = @_;
	defined $self or croak 'uninialize';

	my ( $query, $db_result, $id );
	exists $arg->{term_id} or do {
		error "no term_id";
		return ( 1,undef );
	};
	if ( !exists $arg->{class_id} ) {
		#先查询最大的id
		eval {
			$db_result=$self->{db_conn}->selectall_arrayref('select max(Id) from t_Class');
		} or do {
			#查询失败
			error "database select failed [$@]";
			return ( 1,undef );
		};

		debug Dumper($db_result);
		$#{$db_result} >=0 and $id = $db_result->[0]->[0] + 1;
	}
	else {
		#更新现有班级
		$id = $arg->{class_id};
	}
	my $values = join ',', map { $self->{db_conn}->quote($_) } ( $id, @{$arg}{ qw( term_id class_name ) } );
	$query = "replace into t_Class(Id,Term_Id,Name) values($values)" ;
	debug "sql:[$query]";

	eval {
		$db_result=$self->{db_conn}->do($query);
	} or do {
		error "execute sql[$query] failed [$@]";
		return ( 1,undef );
	};

	my $resp = {
		term_id => $arg->{term_id},
		class_id => $id,
		class_name => $arg->{class_name},
	};

	return (0,$resp);
}

sub get_group {
	my ( $self,$arg ) = @_;
	defined $self or croak 'uninialize';

	#构造SQL并查询DB
	exists $arg->{term_id} and exists $arg->{class_id} or do {
		#没有指定term_id和class_id
		error "term_id or class_id not found";
		return ( 1,undef );
	};
	my $query = 'select Id,Term_Id,Class_Id,Name from t_Group where Term_Id=' . $self->{db_conn}->quote($arg->{term_id}) . ' and Class_Id=' . $self->{db_conn}->quote($arg->{class_id});

	if( exists $arg->{group_id} and $#{ $arg->{group_id} } >= 0 ) {
		$query.= " and Id in ( ".join(',',map { $self->{db_conn}->quote($_) } @{$arg->{group_id}}) . ")";
	}

	my $db_result;
	eval {
		$db_result=$self->{db_conn}->selectall_arrayref($query);
	} or do {
		#查询失败
		error "database select failed [$@]";
		return ( 1,undef );
	};

	my $resp = {
	   	group => [ map { { group_id => $_->[0], term_id => $_->[1], class_id => $_->[2], group_name => $_->[3] } } @{$db_result} ],
	};

	return (0,$resp);
}

sub set_group {
	my ( $self,$arg ) = @_;
	defined $self or croak 'uninialize';

	my ( $query, $db_result, $id );
	( exists $arg->{term_id} and  exists $arg->{class_id} ) or do {
		error "no term_id or class_id";
		return ( 1,undef );
	};
	if ( !exists $arg->{group_id} ) {
		#先查询最大的id
		eval {
			$db_result=$self->{db_conn}->selectall_arrayref('select max(Id) from t_Group');
		} or do {
			#查询失败
			error "database select failed [$@]";
			return ( 1,undef );
		};

		debug Dumper($db_result);
		$#{$db_result} >=0 and $id = $db_result->[0]->[0] + 1;
	}
	else {
		#更新现有班级
		$id = $arg->{group_id};
	}
	my $values = join ',', map { $self->{db_conn}->quote($_) } ( $id, @{$arg}{ qw( term_id class_id group_name ) } );
	$query = "replace into t_Group(Id,Term_Id,Class_Id,Name) values($values)";
	debug "sql:[$query]";

	eval {
		$db_result=$self->{db_conn}->do($query);
	} or do {
		error "execute sql[$query] failed [$@]";
		return ( 1,undef );
	};

	my $resp = {
		group_id => $id,
		term_id => $arg->{term_id},
		class_id => $arg->{class_id},
		group_name => $arg->{group_name},
	};

	return (0,$resp);
}

sub get_evaluation {
	my ( $self,$arg ) = @_;

	#检查参数
	map {
		exists $arg->{$_} or do {
			error 'bad argument [' . Dumper $arg . ']';
			return ( 2,undef );
		};
	} qw( term_id class_id );

	#查询
	my $query = 'select Id,Term_Id,Class_Id,Group_Id,Student_Id,Lesson,Score_Type,Score from t_Score where Term_Id=' . $self->{db_conn}->quote($arg->{term_id}) . ' and Class_Id=' . $self->{db_conn}->quote($arg->{class_id});

	if( exists $arg->{group_id} and $#{ $arg->{group_id} } >= 0 ) {
		$query.= " and Group_Id in ( ".join(',',map { $self->{db_conn}->quote($_) } @{$arg->{group_id}}) . ")";
	}

	my $db_result;
	eval {
		$db_result=$self->{db_conn}->selectall_arrayref($query);
	} or do {
		#查询失败
		error "database select failed [$@]";
		return ( 1,undef );
	};

	my $resp = {
		score => [ map {
			{
				term_id => $_->[1],
				class_id => $_->[2],
				group_id => $_->[3],
				student_id => $_->[4],
				lesson => $_->[5],
				score_type => $_->[6],
				score => $_->[7],
			}
		} @{$db_result} ],
	};

	return (0,$resp);
}

sub add_evaluation {
	my ( $self,$arg ) = @_;

	ref $arg->{score} eq ref [] or do {
		error 'bad argument';
		return ( 2,undef );
	};

	#遍历请求并插入
	foreach my $score_item ( @{$arg->{score}} ) {
		#检查参数
		map {
			exists $score_item->{$_} or do { 
				error 'bad line [' . Dumper $score_item . ']';
				return ( 2,undef );
			}
		} qw( term_id class_id group_id student_id lesson score_type score );

		#先查询最大的id
		my ( $id,$db_result ) = ( 0,undef );
		eval {
			$db_result=$self->{db_conn}->selectall_arrayref('select max(Id) from t_Score');
		} or do {
			#查询失败
			error "database select failed [$@]";
			return ( 1,undef );
		};

		debug Dumper($db_result);
		$#{$db_result} >=0 and $id = $db_result->[0]->[0] + 1;

		#插入表
		my $values = join ',', map { $self->{db_conn}->quote($_); } @{$score_item}{ qw( term_id class_id group_id student_id lesson score_type score ) };
		my $query = "replace into t_Score( Id,Term_Id,Class_Id,Group_Id,Student_Id,Lesson,Score_Type,Score ) values($id,$values)";

		debug "sql:[$query]";

		eval {
			$db_result=$self->{db_conn}->do($query);
		} or do {
			error "execute sql[$query] failed [$@]";
			return ( 1,undef );
		};
	}

	my $resp = {
		total => $#{ $arg->{score} } + 1,
	};

	return ( 0,$resp );
}

sub get_student {
	my ( $self,$arg ) = @_;

	#检查参数
	map {
		exists $arg->{$_} or do {
			error 'bad argument [' . Dumper $arg . ']';
			return ( 2,undef );
		};
	} qw( term_id class_id group_id );

	#查询
	my $query = 'select Id,Term_Id,Class_Id,Group_Id,Name from t_Student where Term_Id=' . $self->{db_conn}->quote($arg->{term_id}) . ' and Class_Id=' . $self->{db_conn}->quote($arg->{class_id}) . ' and Group_Id=' . $self->{db_conn}->quote($arg->{group_id});

	if( exists $arg->{student_id} and $#{ $arg->{student_id} } >= 0 ) {
		$query.= " and Id in ( ".join(',',map { $self->{db_conn}->quote } @{$arg->{student_id}}) . ")";
	}

	debug "sql:[$query]";

	my $db_result;
	eval {
		$db_result=$self->{db_conn}->selectall_arrayref($query);
	} or do {
		#查询失败
		error "database select failed [$@]";
		return ( 1,undef );
	};

	my $resp = {
		student => [ map {
			{
				student_id => $_->[0],
				term_id => $_->[1],
				class_id => $_->[2],
				group_id => $_->[3],
				student_name =>  $_->[4],
			}
		} @{$db_result} ],
	};

	return (0,$resp);
}

sub get_scoretype {
	my ( $self,$arg ) = @_;

	#检查参数
	map {
		exists $arg->{$_} or do {
			error 'bad argument [' . Dumper $arg . ']';
			return ( 2,undef );
		};
	} qw( term_id );

	#查询
	my $query = 'select Id,Term_Id,Name from t_Scoretype where Term_Id=' . $self->{db_conn}->quote($arg->{term_id});

	debug "sql:[$query]";

	my $db_result;
	eval {
		$db_result=$self->{db_conn}->selectall_arrayref($query);
	} or do {
		#查询失败
		error "database select failed [$@]";
		return ( 1,undef );
	};

	my $resp = {
		score_type => [ map {
			{
				score_id => $_->[0],
				term_id => $_->[1],
				score_name =>  $_->[2],
			}
		} @{$db_result} ],
	};

	return (0,$resp);
}

1;
